package com.ty.util;

import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * @ClassName: ExcelUtils 
 * @Description: 数据导出为 excel 表格
 * @author zzx
 *
 * @date 2020年12月8日
 */
public class ExcelUtils {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

 
	public static void createExcel2007(OutputStream out, Map<String,Object> params) {
		try {
//			Workbook wb = new XSSFWorkbook();
			HSSFWorkbook wb = new HSSFWorkbook(); //生成 xlsx
			String headStr = (String) params.get("excel_head_cnname");//标题
			Sheet sheet = wb.createSheet();
			int startR = 0;
			Map<String, CellStyle> styleMap = createStyles(wb);
			if (!StringUtils.isEmpty(headStr)) {				
				CellStyle headStyle = styleMap.get("sheet_title_style");
				
				String[] headArr = headStr.split(",");
				String cellSizeStr = (String) params.get("excel_data_cellSize");//宽度
				if (!StringUtils.isEmpty(cellSizeStr)) {
					String[] cellSizeArr = cellSizeStr.split(",");
					createExcelHeadNormal(sheet, startR, headArr, cellSizeArr, headStyle);
				} else {
					createExcelHeadNormal(sheet, startR, headArr, headStyle);
				}
				startR++;
			}
			String dataNameStr = (String) params.get("excel_data_enname");//内容
			if (!StringUtils.isEmpty(dataNameStr)) {
				CellStyle cellStyle = styleMap.get("cell_normal_style");
				String[] dataEnNameArr = dataNameStr.split(",");
				List<Map<String, String>> dataList = (List<Map<String, String>>) params.get("dataList");
				createExcelBody(sheet, startR, dataEnNameArr, dataList, cellStyle);
			}
			wb.write(out);
		} catch (Exception e) {
			LOGGER.error("","",e);
		}
	}
	public static void createExcelBody(Sheet sheet, int startR, String[] dataEnNameArr,
			List<Map<String, String>> dataList, CellStyle normalCellStyle, CellStyle headerStyle, int newHeaderRow) throws Exception  {
		for (int i = 0, len = dataList.size(); i < len; i++) {
			Row row = sheet.createRow(startR + i); 
			for (int j = 0, jlen = dataEnNameArr.length; j < jlen; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(dataList.get(i).get(dataEnNameArr[j]));
				if((startR + i - 1) == newHeaderRow){
					cell.setCellStyle(headerStyle);
				} else {
					cell.setCellStyle(normalCellStyle);
				}
			}
		}
	}
	public static void createExcelBody(Sheet sheet, int startR, String[] dataEnNameArr,
			List<Map<String, String>> dataList, CellStyle normalCellStyle) throws Exception  {
		for (int i = 0, len = dataList.size(); i < len; i++) {
			Row row = sheet.createRow(startR + i); 
			for (int j = 0, jlen = dataEnNameArr.length; j < jlen; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(dataList.get(i).get(dataEnNameArr[j]));
				cell.setCellStyle(normalCellStyle);
			}
		}
	}
	
	public static void createExcelHeadNormal(Sheet sheet, int startR, String[] headArr, CellStyle headStyle)
			throws Exception  {
		Row row = sheet.createRow(startR);
		row.setHeight((short)500);
		for (int i = 0, len = headArr.length; i < len; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(headArr[i]);
			cell.setCellStyle(headStyle);
		}
		for (int i = 0, len = headArr.length; i < len; i++) {
			sheet.setColumnWidth(i, 20 * 256);
		}
	}
	
	public static void createExcelHeadNormal(Sheet sheet, int startR, String[] headArr, String[] cellSizeArr,
			CellStyle headStyle) throws Exception  {
		Row row = sheet.createRow(startR);
		row.setHeight((short)500);
		for (int i = 0, len = headArr.length; i < len; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(headArr[i]);
			cell.setCellStyle(headStyle);
		}
		for (int i = 0, len = headArr.length; i < len; i++) {
			sheet.setColumnWidth(i,str2Int(cellSizeArr[i], 20) * 256);
		}
	}
	
	public static int str2Int(String s,int defaultVal){
		try{
			return Integer.parseInt(s);
		}catch(Exception e){			
			return defaultVal;
		}
		
	}
	
	// 创建Excel样式
	private static Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> stylesMap = new HashMap<String , CellStyle>();
		
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(true);
		stylesMap.put("cell_normal_style", style);

		Font sheetFont = wb.createFont();
		sheetFont.setFontName("宋体");
		sheetFont.setFontHeightInPoints((short) 12);
		
		
		style = wb.createCellStyle();
		style.setFont(sheetFont);
		style.setWrapText(true);
		style.setFillForegroundColor(IndexedColors.LIME.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		stylesMap.put("sheet_title_style", style);
		
		style = wb.createCellStyle();
		style.setFont(sheetFont);
		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		stylesMap.put("newsheet_title_style", style);
		
		return stylesMap;
	}
}
